﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

//using _WebShared.Classes;

namespace KPIS.GERP.GHRMS.DAL
{
    public class JobSiteCommandDAL : IJobSiteCommandDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());

        public DataTable SelectJobsiteCommandDtls(string job_site_seq)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Int32.Parse(job_site_seq));

                strSQL = "SELECT c.job_site_cmd_seq, c.cmd_draft_no, c.cmd_ref_no"
                    + ", c.cmd_descr, c.cmd_status, approved_by, approved_name, c.approved_date"
                    + ", c.approved_positions, c.record_status, rs.record_status_name"
                    + ", cd.job_site_seq, cd.to_ref_no, cd.to_job_site_name, cd.to_upper_seq"
                    + ", cd.date_start"
                    + " FROM hrm_mp_job_site_commands c"
                    + " LEFT join hrm_mp_job_site_command_dtls cd ON c.job_site_cmd_seq = cd.job_site_cmd_seq"
                    + " INNER JOIN mas_record_statuses rs ON c.record_status = rs.record_status"
                    + " WHERE c.record_status not like @DeleteRecord"
                    + " AND cd.job_site_seq = @Sequence";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataRow Select(string job_site_cmd_seq)
        {
            string strSQL;
            DataRow dr = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Int32.Parse(job_site_cmd_seq));

                strSQL = "SELECT c.job_site_cmd_seq, c.cmd_draft_no, c.cmd_ref_no"
                    + ", c.cmd_descr, c.cmd_status, approved_by, approved_name, c.approved_date"
                    + ", c.approved_positions, c.record_status, rs.record_status_name"
                    + ", coalesce(cd.count_dtls, 0) as count_dtls, c.is_internal"
                    + " FROM hrm_mp_job_site_commands c"
                    + " LEFT JOIN (SELECT job_site_cmd_seq, count(*) as count_dtls"
                        + " FROM hrm_mp_job_site_command_dtls"
                        + " GROUP BY job_site_cmd_seq"
                        + ") cd on c.job_site_cmd_seq = cd.job_site_cmd_seq"
                    + " INNER JOIN mas_record_statuses rs on c.record_status = rs.record_status"
                    + " WHERE c.RECORD_STATUS not like @DeleteRecord"
                        + " AND c.job_site_cmd_seq = @Sequence";

                dr = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dr;
        }

        public void Insert(string cmd_draft_no, string cmd_ref_no, string cmd_descr
            , string cmd_status, string job_site_type, string approved_by, string approved_name
            , string approved_date, string approved_positions, string record_status, string created_by)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@cmd_draft_no", cmd_draft_no);
                dbManager.AddParameters(1, "@cmd_ref_no", cmd_ref_no);
                dbManager.AddParameters(2, "@cmd_descr", cmd_descr);
                dbManager.AddParameters(3, "@cmd_status", cmd_status);
                dbManager.AddParameters(4, "@job_site_type", Int32.Parse(job_site_type));
                dbManager.AddParameters(5, "@approved_by", Int32.Parse(approved_by));
                dbManager.AddParameters(6, "@approved_name", approved_name);
                dbManager.AddParameters(7, "@approved_positions", approved_positions);
                dbManager.AddParameters(8, "@approved_date", DateTime.Parse(approved_date));
                dbManager.AddParameters(9, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(10, "@created_by", Int32.Parse(created_by));
                dbManager.AddParameters(11, "@created_when", DateTime.Today);
                dbManager.AddParameters(12, "@cmd_flag", job_site_type);
                
                strSQL = "INSERT INTO HRM_MP_JOB_SITE_COMMANDS"
                    + "(CMD_DRAFT_NO, CMD_REF_NO, CMD_DESCR, CMD_STATUS, JOB_SITE_TYPE_SEQ"
                    + ", APPROVED_BY, APPROVED_NAME, APPROVED_DATE, APPROVED_POSITIONS"
                    + ", RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES (@cmd_draft_no"
                        + ", @cmd_flag"
                        + ", @cmd_ref_no"
                        + ", @cmd_descr"
                        + ", @cmd_status"
                        + ", @job_site_type"
                        + ", @approved_by"
                        + ", @approved_by"
                        + ", @approved_name"
                        + ", @approved_date"
                        + ", @approved_positions"
                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(string job_site_cmd_seq, string cmd_draft_no, string cmd_ref_no
            , string cmd_descr, string cmd_status, string job_site_type, string approved_by
            , string approved_name, string approved_date, string approved_positions
            , string updated_by)
        {
            string strSQL;
             
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@cmd_draft_no", cmd_draft_no);
                dbManager.AddParameters(1, "@cmd_ref_no", cmd_ref_no);
                dbManager.AddParameters(2, "@cmd_descr", cmd_descr);
                dbManager.AddParameters(3, "@cmd_status", cmd_status);
                dbManager.AddParameters(4, "@job_site_type", Int32.Parse(job_site_type));
                dbManager.AddParameters(5, "@approved_by", Int32.Parse(approved_by));
                dbManager.AddParameters(6, "@approved_name", approved_name);
                dbManager.AddParameters(7, "@approved_positions", approved_positions);
                dbManager.AddParameters(8, "@approved_date", DateTime.Parse(approved_date));
                dbManager.AddParameters(9, "@Sequence", Int32.Parse(job_site_cmd_seq));
                dbManager.AddParameters(10, "@updated_by", Int32.Parse(updated_by));
                dbManager.AddParameters(11, "@updated_when", DateTime.Today);
                dbManager.AddParameters(12, "@cmd_flag", job_site_type);
                
                strSQL = "UPDATE hrm_mp_job_site_commands"
                    + " SET cmd_draft_no = @cmd_ref_no"
                    + ", cmd_flag = @cmd_flag"
                    + ", cmd_ref_no = @cmd_ref_no"
                    + ", cmd_descr = @cmd_descr"
                    + ", cmd_status = @cmd_status"
                    + ", job_site_type_seq = @job_site_type"
                    + ", approved_by = @approved_by"
                    + ", approved_name = @approved_name"
                    + ", approved_date = @approved_date"
                    + ", approved_positions = @approved_positions"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE job_site_cmd_seq = @Sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(string job_site_cmd_seq, string updated_by)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Int32.Parse(job_site_cmd_seq));
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", Int32.Parse(updated_by));
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                strSQL = "UPDATE HRM_MP_JOB_SITE_COMMANDS"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE JOB_SITE_CMD_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
               

        public void Approve(string job_site_cmd_seq, string approved_by, string approved_name
            , string approved_date, string approved_positions, string updated_by)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@Sequence", Int32.Parse(job_site_cmd_seq));
                dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@approved_by", Int32.Parse(approved_by));
                dbManager.AddParameters(3, "@approved_name", approved_name);
                dbManager.AddParameters(4, "@approved_date", DateTime.Parse(approved_date));
                dbManager.AddParameters(5, "@approved_positions", approved_positions);
                dbManager.AddParameters(6, "@updated_by", Int32.Parse(updated_by));
                dbManager.AddParameters(7, "@updated_when", DateTime.Today);

                strSQL = "UPDATE hrm_mp_job_site_commands"
                    + " SET record_status = @record_status"
                        + ", cmd_status = @record_status"
                        + ", approved_by = @approved_by"
                        + ", approved_name = @approved_name"
                        + ", approved_date = @approved_date"
                        + ", approved_positions = @approved_positions"
                        + ", updated_by = @updated_by"
                        + ", updated_when = @updated_when"
                    + " WHERE job_site_cmd_seq = @Sequence";
                
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Cancel(string job_site_cmd_seq, string updated_by)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Int32.Parse(job_site_cmd_seq));
                dbManager.AddParameters(1, "@record_status", SystemInfo._CancelRecordStatus);
                dbManager.AddParameters(2, "@updated_by", Int32.Parse(updated_by));
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                strSQL = "UPDATE HRM_MP_JOB_SITE_COMMANDS"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE JOB_SITE_CMD_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
